﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;

namespace WebApplication1
{
    public partial class _03_SqlCommand : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        /// <summary>
        /// SQLCommand命令Demo
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnCommand_Click(object sender, EventArgs e)
        {
            #region 获取查询结果的第一行第一列数据值
            using (SqlConnection conn = new SqlConnection(DBUnit.ConnStr))
            {
                //直接SQL语句
                string strSql = "select * from Category";
                using (SqlCommand cmd = new SqlCommand(strSql, conn))
                {
                    //打开数据库
                    conn.Open();
                    //执行SQL语句,返回查询结果的第一行第一列的值
                    string strTemp = "Category数据表第一行第一列数据是：";
                    lblObj.Text = strTemp + cmd.ExecuteScalar().ToString();
                }
            }
            #endregion

            #region 获取查询结果的行数
            using (SqlConnection conn = new SqlConnection(DBUnit.ConnStr))
            {
                //直接SQL语句
                string sql = "select count(*) from category";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //打开数据库
                    conn.Open();
                    int count;
                    if (!int.TryParse(cmd.ExecuteScalar().ToString(), out count))
                    {
                        Response.Write("<font color=red>执行sql语句错误!</font>");
                    }
                    //执行SQL语句,返回查询结果的第一行第一列的值
                    string strTemp = "Category数据表的总记录数是：";
                    lblCount.Text = strTemp + cmd.ExecuteScalar().ToString();
                }
            }
            #endregion

            #region 绑定数据到GridView1
            bindGridView();
            #endregion
        }

        /// <summary>
        /// 绑定数据到GridView1
        /// </summary>
        private void bindGridView()
        {
       
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(DBUnit.ConnStr))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter("Pr_GetCategorys", conn))
                {
                    sda.Fill(ds);
                }
            }
            //设置DataSource数据源
            GridView1.DataSource = ds.Tables[0];
            //调用绑定数据的方法DataBind()
            GridView1.DataBind();
         
        }


        /// <summary>
        /// 删除指定CategoryID的数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDelete_Click(object sender, EventArgs e)
        {
            int categoryID = 0;
            if (!Int32.TryParse(txtCategoryID.Text.Trim(), out categoryID))
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('请输入整数!')", true);
                return;
            }

            using (SqlConnection conn = new SqlConnection(DBUnit.ConnStr))
            {
                //Pr_DeleteCategory是删除指定categoryID行数据的存储过程名称
                using (SqlCommand cmd = new SqlCommand("Pr_DeleteCategory", conn))
                {
                    //打开数据库连接
                    conn.Open();

                    #region 添加存储过程参数
                    //新建一个存储过程参数param
                    SqlParameter param = new SqlParameter("@CategoryID", SqlDbType.Int);
                    //为参数赋值
                    param.Value = txtCategoryID.Text.Trim();
                    //将参数添加到SQLcommmand对象
                    cmd.Parameters.Add(param);
                    #endregion

                    //执行存储过程
                    try
                    {
                        cmd.ExecuteNonQuery();
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('删除成功!')", true);
                        //再次绑定GridView数据
                        bindGridView();
                    }
                    catch (Exception ex)
                    {
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('" + ex.Message + "!')", true);
                        return;
                    }
                }
            }//

        }

    }
}